﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Runtime.InteropServices;
using System.Web.Script.Serialization;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using FL.Entity.DB;
using Newtonsoft.Json;
using FL.Entity.DB.Model;
using System.Data;
using FL.DAL;
using System.Text;


namespace FL.Test
{
    [TestClass]
    public class ToExcelStrTest
    {
        [TestMethod]
        public void TestMethod1()
        {
            Share share = new Share();
            ArrayList data = new ArrayList();
            List<string> columns = new List<string>();

            columns.Add("{header: \"报关日期\", field: \"apply_dt\"}");
            columns.Add("{header: \"经营单位\", field: \"shipper_name\"}");
            columns.Add("{header: \"报关费\",  field:\"[ {header:'首柜', field: 'apply_dt'},{header: '续柜', field:'apply_dt'}]\"}");
            columns.Add("{header: \"海关查柜\", field: \"apply_dt\"}");

            var htmstr = share.GetComplexExcelStr(data, columns);
            string aa = htmstr;

        }

        private DataTable GetData()
        {
            DataTable dt = new DataTable();
            DataColumn dc = new DataColumn("序号");
            dt.Columns.Add(dc);
            dc = new DataColumn("日期");
            dt.Columns.Add(dc);
            dc = new DataColumn("经营单位");
            dt.Columns.Add(dc);
            dc = new DataColumn("货物");
            dt.Columns.Add(dc);
            dc = new DataColumn("性质");
            dt.Columns.Add(dc);
            dc = new DataColumn("报关单");
            dt.Columns.Add(dc);
            dc = new DataColumn("业务号");
            dt.Columns.Add(dc);
            dc = new DataColumn("订舱号");
            dt.Columns.Add(dc);
            dc = new DataColumn("合同协议号");
            dt.Columns.Add(dc);
            dc = new DataColumn("车牌号");
            dt.Columns.Add(dc);
            dc = new DataColumn("柜号");
            dt.Columns.Add(dc);
            dc = new DataColumn("20");
            dt.Columns.Add(dc);
            dc = new DataColumn("40");
            dt.Columns.Add(dc);
            dc = new DataColumn("首柜");
            dt.Columns.Add(dc);
            dc = new DataColumn("序柜");
            dt.Columns.Add(dc);
            dc = new DataColumn("序页");
            dt.Columns.Add(dc);
            dc = new DataColumn("快速扫描费");
            dt.Columns.Add(dc);
            dc = new DataColumn("查柜费");
            dt.Columns.Add(dc);
            dc = new DataColumn("改船费");
            dt.Columns.Add(dc);
            dc = new DataColumn("报检费");
            dt.Columns.Add(dc);
            dc = new DataColumn("其他");
            dt.Columns.Add(dc);
            dc = new DataColumn("港建费");
            dt.Columns.Add(dc);
            dc = new DataColumn("港建税点");
            dt.Columns.Add(dc);
            dc = new DataColumn("保安费");
            dt.Columns.Add(dc);
            dc = new DataColumn("理货费");
            dt.Columns.Add(dc);
            dc = new DataColumn("小计");
            dt.Columns.Add(dc);
            dc = new DataColumn("出口口岸");
            dt.Columns.Add(dc);

            for (int i = 0; i < 2; i++)
            {
                DataRow dr = dt.NewRow();
                dr["序号"] = i + 1;
                dr["日期"] = DateTime.Now.ToString("yyyy-MM-dd");
                dr["经营单位"] = "深圳市亚辉龙生物科技股份有限公司";
                dr["货物"] = "TEMU7584318/40'";
                dr["性质"] = "其他费用";
                dr["报关单"] = "1-07633";
                dr["业务号"] = "HW2016043000396";
                dr["订舱号"] = "053426987-7";
                dr["合同协议号"] = "002100034A0001190A";
                dr["车牌号"] = "";
                dr["柜号"] = "MEDU8430021";
                dr["20"] = 1;
                dr["40"] = 1;
                dr["首柜"] = 200;
                dr["序柜"] = 20;
                dr["序页"] = 50;
                dr["快速扫描费"] = 70;
                dr["查柜费"] = 30;
                dr["改船费"] = 180;
                dr["报检费"] = 150;
                dr["其他"] = 15;
                dr["港建费"] = 96;
                dr["港建税点"] = 5.76;
                dr["保安费"] = 15;
                dr["理货费"] = 40;
                dr["小计"] = 165;
                dr["出口口岸"] = "蛇口海关";
                dt.Rows.Add(dr);

            }
            return dt;
        }

        private DataTable GetExeclSet()
        {
            DataTable dt = new DataTable();
            DataColumn dc = new DataColumn("序号");
            dt.Columns.Add(dc);
            dc = new DataColumn("merge_name");
            dt.Columns.Add(dc);
            dc = new DataColumn("sort");
            dt.Columns.Add(dc);
            for (int i = 0; i < 10; i++)
            {
                DataRow dr = dt.NewRow();
                dr["merge_name"] = "";
                if(i>2&&i<5)
                { 
                    dr["merge_name"] = "测试";
                }
                if (i > 5 && i < 9)
                {
                    dr["merge_name"] = "测试2";
                }
                dr["sort"] = i;
                dt.Rows.Add(dr);
            }
            return dt;
        }

        [TestMethod]
        public void OutputExecl()
        {
            DataTable dt = GetData();
            DataTable dtConfig = GetExeclSet();

            FL.Common.ExcelExportNpoi.DataClass.ExcelSet excelSet =
            FL.Common.ExcelExportNpoi.NPOIExcelHelper.ConvertConfig(dtConfig, "测试", "华商联", true, "客户....", true);

            ///FL.Common.ExcelExportNpoi.DataClass.ExcelSet excelSet = new  Common.ExcelExportNpoi.DataClass.ExcelSet
            //{
            //    sheetName = "单据名称",
            //    isNeedHeader = true,//是否需要显示表头
            //    strHeaderText = "华商联报关公司",
            //    isFreezeHeader = true,
            //    headerFont = 20,
            //    isNeedSecondHeader = true,
            //    strSecondHeaderText = "客户:TCL海外  ()" + "第二客户：" + "联系人:王婕" + "Tel(Fax):0755-26852196" + "结算单号:JSHYHW2016061700021" + "计费员:林媛媛",
            //    isFreezeSecondHeader = true,
            //    secondHeaderFont = 14,
            //    isMergeCell = true,
            //    MergeCellValue = "合并輎:2,4;ha:6,8"
            //};

            //FL.Common.ExcelExportNpoi.NPOIExcelHelper.DataTableToExcel(dt, excelSet,"d:/temp.xls");
            //System.IO.MemoryStream stream = FL.Common.ExcelExportNpoi.NPOIExcelHelper.DataTableToExcel(dt, excelSet);

        }
        [TestMethod]
        public void OutputExecl2()
        {
            var query = "{\"dept_id\":\"\",\"beginApplyDt\":\"2016-06-01\",\"endApplyDt\":\"2016-06-03\",\"customer_name\":\"\",\"customer_id\":\"\",\"declare_id\":\"\",\"shipper_name\":\"\",\"invoice\":\"\",\"vessel\":\"\",\"declareGroup\":\"\",\"voyage\":\"\",\"goods_name\":\"\",\"jsy_no\":\"\",\"wharf_id\":\"\",\"print\":\"0\",\"port_name\":\"\",\"port_no\":\"\",\"trade_name\":\"\",\"trade_no\":\"\",\"apply_no\":\"\",\"container_no\":\"\",\"billType\":\"1,2,3\",\"billName\":\"报关费-保安费-港建费\",\"billNo\":\"\",\"billStatus\":\"0\",\"selectId\":\"\",\"customSumField\":\"container20,container40,first_container_no,continue_no,hgcg_cost,bj_cost,jy_cost,sjcg_cost,dd_cost,lihuo_cost,other_cost,wharf_cost,gjsd_cost,protect_cost,total1\",\"finSumField\":\"container_num,apply_cost,dl_cost,dad_cost,port_cost,bh_cost,sjdd_cost,daidian_cost,diandai_cost,kd_cost,lihuo_cost,wts_cost,wharf_cost,protect_cost,fp_cost,total1\"}";
            var queryObj = Common.Globals.JsonToHashTalbe(query);

            OceanMonthStatementParameter oecan = new OceanMonthStatementParameter(queryObj);
            string tmpType ="v_fin_CustomerExportTmp";// string.IsNullOrEmpty(Request["tmpType"])
                //? "v_fin_ByOceanMonthlyClientTmp"
                //: Request["tmpType"];
            // string viewName = string.IsNullOrEmpty(Request["viewName"]) ? "v_fin_ByOceanMonthlyClientTmp" : Request["viewName"];//视图名称
            string exportName = "出口标准";//Request["exportName"]; //execl名称
            //if (!string.IsNullOrEmpty(oecan.DeptId) || !string.IsNullOrEmpty(oecan.CustomerId))
            //{
            //    string companyCode = reconciliation.GetCodeByCompanyName(oecan.DeptId);
            //    companyCode = companyCode == "" ? reconciliation.GetShortCompanyName(oecan.CustomerId) : companyCode;
            //    exportName += companyCode;
            //}

            var tmpId ="91";// string.IsNullOrEmpty(Request["tmpId"]) ? "0" : Request["tmpId"]; //导出模板ID

            DataTable dtConfig = NewGetTmpJsonStr(tmpId);//列配置
            var strSql = GetTmpSqlStr(oecan, tmpId, tmpType); //通过条件获取sql语句
            DataTable data = GetTmpData(oecan, tmpId, tmpType); //通过条件获取sql语句
            FL.Common.ExcelExportNpoi.DataClass.ExcelSet excelSet =
            FL.Common.ExcelExportNpoi.NPOIExcelHelper.ConvertConfig(dtConfig, "测试", "华商联", true, "客户....", true);
            //FL.Common.ExcelExportNpoi.NPOIExcelHelper.DataTableToExcel(data, excelSet, "d:/temp1.xls");
            //byte[] arr = FL.Common.ExcelExportNpoi.NPOIExcelHelper.DataTableToExcel(excelSet, data);
        }

        /// <summary>
        /// 根据模板拼接不同的格式sql语句 add by 钟星星 2016-1-14
        /// </summary>
        /// <param name="tmpId">模板id</param>

        /// <param name="tmpType">模板类型</param>
        /// <returns></returns>
        public string GetTmpSqlStr(OceanMonthStatementParameter oecan, string tmpId, string tmpType)
        {
            var orderField = "";
            DataTable dt = sqlhelp.ExecuteQuery(string.Format("select custom_name,field_names, field_reduce_names   from t_customer_fees_tmp_infos where tmp_id={0} order by sort asc", tmpId));
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                if (dt.Rows[i]["custom_name"].ToString().Trim() != "序号")
                {
                    dt.Rows[i]["field_names"] = dt.Rows[i]["field_names"].ToString().Replace(',', '+');
                    if (dt.Rows[i]["field_reduce_names"].ToString() != "")
                    {
                        dt.Rows[i]["field_names"] = dt.Rows[i]["field_names"] + "-" + dt.Rows[i]["field_reduce_names"].ToString().Replace(',', '-');
                    }
                }
                else
                {
                    orderField = dt.Rows[i]["field_names"].ToString();
                    if (orderField.Length > 0)
                    {
                        var overIndex = orderField.IndexOf("over") + 5;
                        orderField = orderField.Substring(overIndex, orderField.Length - 1 - overIndex);
                    }
                }

            }
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("select ");
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                string str = "";
                string field_name = dt.Rows[i]["field_names"].ToString();
                string customer_field_name = dt.Rows[i]["custom_name"].ToString();

                field_name = string.IsNullOrEmpty(field_name.Trim()) ? "''" : field_name;
                if (i == 0)
                {

                    str = field_name + " [" + customer_field_name + "]";
                }
                else
                {
                    str = "," + field_name + " [" + customer_field_name + "]";
                }

                sqlStr.Append(str);
            }
            sqlStr.Append(" from " + tmpType + " where 1=1 ");

            if (oecan.BillStatus != 2)
            {
                //报关费
                if (oecan.BillType.Contains("1"))
                {
                    sqlStr.AppendFormat(" and  b1= {0}", oecan.BillStatus);
                }
                //保安费
                if (oecan.BillType.Contains("2"))
                {
                    sqlStr.AppendFormat(" and b2= {0}", oecan.BillStatus);
                }
                //港建费
                if (oecan.BillType.Contains("3"))
                {
                    sqlStr.AppendFormat(" and b3= {0}", oecan.BillStatus);
                }
            }
            if (!string.IsNullOrEmpty(oecan.BillNo)) //结算单号
            {
                sqlStr.AppendFormat(" and billno= '{0}'", oecan.BillNo);
            }
            if (!string.IsNullOrEmpty(oecan.DeptId)) //所属公司
            {
                sqlStr.AppendFormat(" and company_name= '{0}'", oecan.DeptId);
            }
            if (!string.IsNullOrEmpty(oecan.DeclareGroup)) //所属报关组
            {
                sqlStr.AppendFormat(" and dept_id= '{0}'", oecan.DeclareGroup);
            }
            if (!string.IsNullOrEmpty(oecan.BeginApplyDt))
            {
                sqlStr.AppendFormat(" and apply_dt>='{0}'", oecan.BeginApplyDt);
            }
            if (!string.IsNullOrEmpty(oecan.EndApplyDt))
            {
                sqlStr.AppendFormat(" and apply_dt<= '{0}'", oecan.EndApplyDt);
            }
            if (!string.IsNullOrEmpty(oecan.CustomerName))
            {
                sqlStr.AppendFormat(" and customer_short like'%{0}%'", oecan.CustomerName);
            }
            if (!string.IsNullOrEmpty(oecan.CustomerId))
            {
                sqlStr.AppendFormat(" and customer_id= {0}", oecan.CustomerId);
            }
            if (!string.IsNullOrEmpty(oecan.DeclareId))
            {
                sqlStr.AppendFormat(" and declare_id= {0}", oecan.DeclareId);
            }
            if (!string.IsNullOrEmpty(oecan.ShipperName))
            {
                sqlStr.AppendFormat(" and shipper_name like'%{0}%'", oecan.ShipperName);
            }
            if (!string.IsNullOrEmpty(oecan.ShipperNo))
            {
                sqlStr.AppendFormat(" and shipper_no= '{0}'", oecan.ShipperNo);
            }
            if (!string.IsNullOrEmpty(oecan.Invoice))
            {
                sqlStr.AppendFormat(" and bill_no like'%{0}%'", oecan.Invoice);
            }
            if (!string.IsNullOrEmpty(oecan.Vessel))
            {
                sqlStr.AppendFormat(" and vessel like'%{0}%'", oecan.Vessel);
            }
            if (!string.IsNullOrEmpty(oecan.Voyage))
            {
                sqlStr.AppendFormat(" and voyage like'%{0}%'", oecan.Voyage);
            }
            if (!string.IsNullOrEmpty(oecan.GoodsName))
            {
                sqlStr.AppendFormat(" and goods_name like'%{0}%'", oecan.GoodsName);
            }
            if (!string.IsNullOrEmpty(oecan.JsyNo))
            {
                sqlStr.AppendFormat(" and jsy_no= '{0}'", oecan.JsyNo);
            }
            if (!string.IsNullOrEmpty(oecan.WharfId))
            {
                sqlStr.AppendFormat(" and wharf_id in ({0})", oecan.WharfId);
            }
            if (!string.IsNullOrEmpty(oecan.Print))
            {
                sqlStr.AppendFormat(" and [print]= '{0}'", oecan.Print);
            }
            if (!string.IsNullOrEmpty(oecan.PortName))
            {
                sqlStr.AppendFormat(" and port_name like'%{0}%'", oecan.PortName);
            }
            if (!string.IsNullOrEmpty(oecan.PortNo))
            {
                sqlStr.AppendFormat(" and port_no= '{0}'", oecan.PortNo);
            }
            if (!string.IsNullOrEmpty(oecan.TradeName))
            {
                sqlStr.AppendFormat(" and trade_name like'%{0}%'", oecan.TradeName);
            }
            if (!string.IsNullOrEmpty(oecan.TradeNo))
            {
                sqlStr.AppendFormat(" and trade_no= '{0}'", oecan.TradeNo);
            }
            if (!string.IsNullOrEmpty(oecan.ApplyNo))
            {
                if (oecan.ApplyNo.IndexOf(",") > 0)
                {
                    string[] applyNo = oecan.ApplyNo.Split(',');
                    sqlStr.Append("and (");
                    for (int i = 0; i < applyNo.Length; i++)
                    {
                        if (i == 0)
                        {
                            // sqlStr += " apply_no like'%" + applyNo[i] + "%'";
                            sqlStr.AppendFormat("  apply_no like'%{0}%'", applyNo[i]);
                        }
                        else
                        {
                            //   sqlStr += " or apply_no like'%" + applyNo[i] + "%'";
                            sqlStr.AppendFormat(" or apply_no like'%{0}%'", applyNo[i]);
                        }

                    }
                    sqlStr.Append(" )");
                }
                else
                {
                    //   sqlStr += " and apply_no like'%" + oecan.ApplyNo + "%'";
                    sqlStr.AppendFormat(" and apply_no like'%{0}%'", oecan.ApplyNo);
                }

            }

            if (!string.IsNullOrEmpty(oecan.ContainerNo))
            {
                if (oecan.ContainerNo.IndexOf(",") > 0)
                {
                    string[] applyNo = oecan.ContainerNo.Split(',');
                    sqlStr.Append("and (");
                    for (int i = 0; i < applyNo.Length; i++)
                    {
                        if (i == 0)
                        {
                            //  sqlStr += " container_no like'%" + applyNo[i] + "%'";
                            sqlStr.AppendFormat("  container_no like'%{0}%'", applyNo[i]);
                        }
                        else
                        {
                            //sqlStr += " or container_no like'%" + applyNo[i] + "%'";
                            sqlStr.AppendFormat(" or container_no like'%{0}%'", applyNo[i]);
                        }

                    }
                    sqlStr.Append(" )");
                }
                else
                {
                    // sqlStr += " and container_no like'%" + oecan.ContainerNo + "%'";
                    sqlStr.AppendFormat(" and container_no like'%{0}%'", oecan.ContainerNo);
                }
            }

            sqlStr.Append(orderField);


            return sqlStr.ToString();
        }

        /// <summary>
        /// 根据模板拼接不同的格式sql语句 add by 钟星星 2016-1-14
        /// </summary>
        /// <param name="tmpId">模板id</param>

        /// <param name="tmpType">模板类型</param>
        /// <returns></returns>
        public DataTable GetTmpData(OceanMonthStatementParameter oecan, string tmpId, string tmpType)
        {
            string sql = GetTmpSqlStr(oecan, tmpId, tmpType);
            return new DbSql().Query(sql).Tables[0];
        }

        private FL.DAL.DbSql sqlhelp = new DbSql();


        public DataTable NewGetTmpJsonStr(string tmpId)
        {
            DataTable dt =
                sqlhelp.ExecuteQuery(string.Format("select  merge_name,sort,sort 序号 from t_customer_fees_tmp_infos where tmp_id={0} order by sort asc", tmpId));
            return dt;
            //for (int i = 0; i < dt.Rows.Count; i++)
            //{
            //    //存在合并的列
            //    if (dt.Rows[i]["merge_name"].ToString() != "")
            //    {
            //        dt.Rows[i]["field"] = GetFields(dt.Rows[i]["merge_name"].ToString(), dt);
            //    }

            //}
            //DataTable dt1 = FilterTable(dt);
            //return FL.Common.Globals.DataTableToJSON(dt1);
        }



        [TestMethod]
        public void TestMethod2()
        {
            Share share = new Share();
            ArrayList data = new ArrayList();
            string str = "[{\"header\":\"序号\",\"width\":\"40px\",\"type\":\"indexcolumn\",\"cellCls\":\"\",\"align\":\"center\",\"draggable\":false,\"allowDrag\":true,\"hideable\":true,\"_id\":1,\"_pid\":-1,\"_level\":0,\"visible\":true,\"allowResize\":true,\"allowMove\":true,\"allowSort\":false,\"readOnly\":false,\"autoEscape\":false,\"enabled\":true,\"vtype\":\"\",\"inited\":true,\"_gridUID\":\"mini-19\",\"_rowIdField\":\"_id\",\"_index\":0,\"summaryValue\":\"\",\"rowspan\":2,\"colspan1\":1,\"colspan2\":1,\"colspan0\":1,\"viewIndex1\":true},{\"type\":\"checkcolumn\",\"width\":\"30px\",\"cellCls\":\"mini-checkcolumn\",\"headerCls\":\"mini-checkcolumn\",\"hideable\":true,\"_multiRowSelect\":true,\"_id\":2,\"_pid\":-1,\"_level\":0,\"visible\":true,\"allowResize\":true,\"allowMove\":true,\"allowSort\":false,\"allowDrag\":false,\"readOnly\":false,\"autoEscape\":false,\"enabled\":true,\"vtype\":\"\",\"inited\":true,\"_gridUID\":\"mini-19\",\"_rowIdField\":\"_id\",\"_index\":1,\"summaryValue\":\"\",\"rowspan\":2,\"colspan1\":1,\"colspan2\":1,\"colspan0\":1,\"viewIndex1\":true},{\"header\":\"日期\",\"field\":\"apply_dt\",\"width\":\"60px\",\"headerAlign\":\"center\",\"dateFormat\":\"yyyy-MM-dd\",\"_id\":3,\"_pid\":-1,\"_level\":0,\"visible\":true,\"allowResize\":true,\"allowMove\":true,\"allowSort\":false,\"allowDrag\":false,\"readOnly\":false,\"autoEscape\":false,\"enabled\":true,\"vtype\":\"\",\"inited\":true,\"_gridUID\":\"mini-19\",\"_rowIdField\":\"_id\",\"_index\":2,\"summaryValue\":\"\",\"rowspan\":2,\"colspan1\":1,\"colspan2\":1,\"colspan0\":1,\"viewIndex1\":true},{\"header\":\"经营单位\",\"field\":\"shipper_name\",\"width\":\"120px\",\"headerAlign\":\"center\",\"_id\":4,\"_pid\":-1,\"_level\":0,\"visible\":true,\"allowResize\":true,\"allowMove\":true,\"allowSort\":false,\"allowDrag\":false,\"readOnly\":false,\"autoEscape\":false,\"enabled\":true,\"vtype\":\"\",\"inited\":true,\"_gridUID\":\"mini-19\",\"_rowIdField\":\"_id\",\"_index\":3,\"summaryValue\":\"\",\"rowspan\":2,\"colspan1\":1,\"colspan2\":1,\"colspan0\":1,\"viewIndex1\":true},{\"header\":\"货名\",\"field\":\"goods_name\",\"width\":\"120px\",\"headerAlign\":\"center\",\"_id\":5,\"_pid\":-1,\"_level\":0,\"visible\":true,\"allowResize\":true,\"allowMove\":true,\"allowSort\":false,\"allowDrag\":false,\"readOnly\":false,\"autoEscape\":false,\"enabled\":true,\"vtype\":\"\",\"inited\":true,\"_gridUID\":\"mini-19\",\"_rowIdField\":\"_id\",\"_index\":4,\"summaryValue\":\"\",\"rowspan\":2,\"colspan1\":1,\"colspan2\":1,\"colspan0\":1,\"viewIndex1\":true},{\"header\":\"性质\",\"field\":\"property\",\"width\":\"60px\",\"headerAlign\":\"center\",\"_id\":6,\"_pid\":-1,\"_level\":0,\"visible\":true,\"allowResize\":true,\"allowMove\":true,\"allowSort\":false,\"allowDrag\":false,\"readOnly\":false,\"autoEscape\":false,\"enabled\":true,\"vtype\":\"\",\"inited\":true,\"_gridUID\":\"mini-19\",\"_rowIdField\":\"_id\",\"_index\":5,\"summaryValue\":\"\",\"rowspan\":2,\"colspan1\":1,\"colspan2\":1,\"colspan0\":1,\"viewIndex1\":true},{\"header\":\"\n                                柜号\n                            \",\"field\":\"container_no\",\"width\":\"80px\",\"headerAlign\":\"center\",\"_id\":7,\"_pid\":-1,\"_level\":0,\"visible\":true,\"allowResize\":true,\"allowMove\":true,\"allowSort\":false,\"allowDrag\":false,\"readOnly\":false,\"autoEscape\":false,\"enabled\":true,\"vtype\":\"\",\"inited\":true,\"_gridUID\":\"mini-19\",\"_rowIdField\":\"_id\",\"_index\":6,\"summaryValue\":\"\",\"rowspan\":2,\"colspan1\":1,\"colspan2\":1,\"colspan0\":1,\"viewIndex1\":true},{\"header\":\"订舱号\",\"field\":\"book_no\",\"width\":\"80px\",\"headerAlign\":\"center\",\"_id\":8,\"_pid\":-1,\"_level\":0,\"visible\":true,\"allowResize\":true,\"allowMove\":true,\"allowSort\":false,\"allowDrag\":false,\"readOnly\":false,\"autoEscape\":false,\"enabled\":true,\"vtype\":\"\",\"inited\":true,\"_gridUID\":\"mini-19\",\"_rowIdField\":\"_id\",\"_index\":7,\"summaryValue\":\"\",\"rowspan\":2,\"colspan1\":1,\"colspan2\":1,\"colspan0\":1,\"viewIndex1\":true},{\"columns\":[{\"header\":\"20\",\"field\":\"container20\",\"width\":\"40px\",\"headerAlign\":\"center\",\"align\":\"center\",\"summaryType\":\"sum\",\"_id\":10,\"_pid\":9,\"_level\":1,\"visible\":true,\"allowResize\":true,\"allowMove\":true,\"allowSort\":false,\"allowDrag\":false,\"readOnly\":false,\"autoEscape\":false,\"enabled\":true,\"vtype\":\"\",\"inited\":true,\"_gridUID\":\"mini-19\",\"_rowIdField\":\"_id\",\"_index\":8,\"summaryValue\":0,\"colspan1\":1,\"colspan2\":1,\"colspan0\":1,\"viewIndex2\":true},{\"header\":\"40\",\"field\":\"container40\",\"width\":\"40px\",\"headerAlign\":\"center\",\"align\":\"center\",\"summaryType\":\"sum\",\"_id\":11,\"_pid\":9,\"_level\":1,\"visible\":true,\"allowResize\":true,\"allowMove\":true,\"allowSort\":false,\"allowDrag\":false,\"readOnly\":false,\"autoEscape\":false,\"enabled\":true,\"vtype\":\"\",\"inited\":true,\"_gridUID\":\"mini-19\",\"_rowIdField\":\"_id\",\"_index\":9,\"summaryValue\":2,\"colspan1\":1,\"colspan2\":1,\"colspan0\":1,\"viewIndex2\":true}],\"header\":\"柜数\",\"headerAlign\":\"center\",\"_id\":9,\"_pid\":-1,\"_level\":0,\"width\":\"100px\",\"visible\":true,\"allowResize\":true,\"allowMove\":true,\"allowSort\":false,\"allowDrag\":false,\"readOnly\":false,\"autoEscape\":false,\"enabled\":true,\"vtype\":\"\",\"inited\":true,\"_gridUID\":\"mini-19\",\"_rowIdField\":\"_id\",\"colspan\":2,\"colspan1\":0,\"colspan2\":2,\"colspan0\":0,\"viewIndex2\":true},{\"columns\":[{\"header\":\"首柜\",\"field\":\"first_container_no\",\"width\":\"50px\",\"headerAlign\":\"center\",\"align\":\"center\",\"dataType\":\"currency\",\"summaryType\":\"sum\",\"_id\":13,\"_pid\":12,\"_level\":1,\"visible\":true,\"allowResize\":true,\"allowMove\":true,\"allowSort\":false,\"allowDrag\":false,\"readOnly\":false,\"autoEscape\":false,\"enabled\":true,\"vtype\":\"\",\"inited\":true,\"_gridUID\":\"mini-19\",\"_rowIdField\":\"_id\",\"_index\":10,\"summaryValue\":50,\"colspan1\":1,\"colspan2\":1,\"colspan0\":1,\"viewIndex2\":true},{\"header\":\"续柜\",\"field\":\"continue_no\",\"width\":\"50px\",\"headerAlign\":\"center\",\"align\":\"center\",\"dataType\":\"currency\",\"summaryType\":\"sum\",\"_id\":14,\"_pid\":12,\"_level\":1,\"visible\":true,\"allowResize\":true,\"allowMove\":true,\"allowSort\":false,\"allowDrag\":false,\"readOnly\":false,\"autoEscape\":false,\"enabled\":true,\"vtype\":\"\",\"inited\":true,\"_gridUID\":\"mini-19\",\"_rowIdField\":\"_id\",\"_index\":11,\"summaryValue\":20,\"colspan1\":1,\"colspan2\":1,\"colspan0\":1,\"viewIndex2\":true}],\"header\":\"报关费\",\"headerAlign\":\"center\",\"_id\":12,\"_pid\":-1,\"_level\":0,\"width\":\"100px\",\"visible\":true,\"allowResize\":true,\"allowMove\":true,\"allowSort\":false,\"allowDrag\":false,\"readOnly\":false,\"autoEscape\":false,\"enabled\":true,\"vtype\":\"\",\"inited\":true,\"_gridUID\":\"mini-19\",\"_rowIdField\":\"_id\",\"colspan\":2,\"colspan1\":0,\"colspan2\":2,\"colspan0\":0,\"viewIndex2\":true},{\"header\":\"海关查柜费\",\"field\":\"hgcg_cost\",\"width\":\"60px\",\"headerAlign\":\"center\",\"align\":\"center\",\"dataType\":\"currency\",\"summaryType\":\"sum\",\"_id\":15,\"_pid\":-1,\"_level\":0,\"visible\":true,\"allowResize\":true,\"allowMove\":true,\"allowSort\":false,\"allowDrag\":false,\"readOnly\":false,\"autoEscape\":false,\"enabled\":true,\"vtype\":\"\",\"inited\":true,\"_gridUID\":\"mini-19\",\"_rowIdField\":\"_id\",\"_index\":12,\"summaryValue\":24.2,\"rowspan\":2,\"colspan1\":1,\"colspan2\":1,\"colspan0\":1,\"viewIndex2\":true},{\"header\":\"报检费\",\"field\":\"bj_cost\",\"width\":\"60px\",\"headerAlign\":\"center\",\"align\":\"center\",\"dataType\":\"currency\",\"summaryType\":\"sum\",\"_id\":16,\"_pid\":-1,\"_level\":0,\"visible\":true,\"allowResize\":true,\"allowMove\":true,\"allowSort\":false,\"allowDrag\":false,\"readOnly\":false,\"autoEscape\":false,\"enabled\":true,\"vtype\":\"\",\"inited\":true,\"_gridUID\":\"mini-19\",\"_rowIdField\":\"_id\",\"_index\":13,\"summaryValue\":0,\"rowspan\":2,\"colspan1\":1,\"colspan2\":1,\"colspan0\":1,\"viewIndex2\":true},{\"header\":\"检疫费\",\"field\":\"jy_cost\",\"width\":\"60px\",\"headerAlign\":\"center\",\"align\":\"center\",\"dataType\":\"currency\",\"summaryType\":\"sum\",\"_id\":17,\"_pid\":-1,\"_level\":0,\"visible\":true,\"allowResize\":true,\"allowMove\":true,\"allowSort\":false,\"allowDrag\":false,\"readOnly\":false,\"autoEscape\":false,\"enabled\":true,\"vtype\":\"\",\"inited\":true,\"_gridUID\":\"mini-19\",\"_rowIdField\":\"_id\",\"_index\":14,\"summaryValue\":6.8,\"rowspan\":2,\"colspan1\":1,\"colspan2\":1,\"colspan0\":1,\"viewIndex2\":true},{\"header\":\"商检查柜费\",\"field\":\"sjcg_cost\",\"width\":\"60px\",\"headerAlign\":\"center\",\"align\":\"center\",\"dataType\":\"currency\",\"summaryType\":\"sum\",\"_id\":18,\"_pid\":-1,\"_level\":0,\"visible\":true,\"allowResize\":true,\"allowMove\":true,\"allowSort\":false,\"allowDrag\":false,\"readOnly\":false,\"autoEscape\":false,\"enabled\":true,\"vtype\":\"\",\"inited\":true,\"_gridUID\":\"mini-19\",\"_rowIdField\":\"_id\",\"_index\":15,\"summaryValue\":15,\"rowspan\":2,\"colspan1\":1,\"colspan2\":1,\"colspan0\":1,\"viewIndex2\":true},{\"header\":\"代垫费\",\"field\":\"dd_cost\",\"width\":\"60px\",\"headerAlign\":\"center\",\"align\":\"center\",\"dataType\":\"currency\",\"summaryType\":\"sum\",\"_id\":19,\"_pid\":-1,\"_level\":0,\"visible\":true,\"allowResize\":true,\"allowMove\":true,\"allowSort\":false,\"allowDrag\":false,\"readOnly\":false,\"autoEscape\":false,\"enabled\":true,\"vtype\":\"\",\"inited\":true,\"_gridUID\":\"mini-19\",\"_rowIdField\":\"_id\",\"_index\":16,\"summaryValue\":2,\"rowspan\":2,\"colspan1\":1,\"colspan2\":1,\"colspan0\":1,\"viewIndex2\":true},{\"header\":\"理货费\",\"field\":\"lihuo_cost\",\"width\":\"60px\",\"headerAlign\":\"center\",\"align\":\"center\",\"dataType\":\"currency\",\"summaryType\":\"sum\",\"_id\":20,\"_pid\":-1,\"_level\":0,\"visible\":true,\"allowResize\":true,\"allowMove\":true,\"allowSort\":false,\"allowDrag\":false,\"readOnly\":false,\"autoEscape\":false,\"enabled\":true,\"vtype\":\"\",\"inited\":true,\"_gridUID\":\"mini-19\",\"_rowIdField\":\"_id\",\"_index\":17,\"summaryValue\":0,\"rowspan\":2,\"colspan1\":1,\"colspan2\":1,\"colspan0\":1,\"viewIndex2\":true},{\"header\":\"其他\",\"field\":\"other_cost\",\"width\":\"60px\",\"headerAlign\":\"center\",\"align\":\"center\",\"dataType\":\"currency\",\"summaryType\":\"sum\",\"_id\":21,\"_pid\":-1,\"_level\":0,\"visible\":true,\"allowResize\":true,\"allowMove\":true,\"allowSort\":false,\"allowDrag\":false,\"readOnly\":false,\"autoEscape\":false,\"enabled\":true,\"vtype\":\"\",\"inited\":true,\"_gridUID\":\"mini-19\",\"_rowIdField\":\"_id\",\"_index\":18,\"summaryValue\":0,\"rowspan\":2,\"colspan1\":1,\"colspan2\":1,\"colspan0\":1,\"viewIndex2\":true},{\"header\":\"港建费\",\"name\":\"wharf_cost\",\"field\":\"wharf_cost\",\"width\":\"60px\",\"headerAlign\":\"center\",\"align\":\"center\",\"dataType\":\"currency\",\"summaryType\":\"sum\",\"_id\":22,\"_pid\":-1,\"_level\":0,\"visible\":true,\"allowResize\":true,\"allowMove\":true,\"allowSort\":false,\"allowDrag\":false,\"readOnly\":false,\"autoEscape\":false,\"enabled\":true,\"vtype\":\"\",\"inited\":true,\"_gridUID\":\"mini-19\",\"_rowIdField\":\"_id\",\"_index\":19,\"summaryValue\":0,\"rowspan\":2,\"colspan1\":1,\"colspan2\":1,\"colspan0\":1,\"viewIndex2\":true},{\"header\":\"港建税点\",\"name\":\"gjsd_cost\",\"field\":\"gjsd_cost\",\"width\":\"60px\",\"headerAlign\":\"center\",\"align\":\"center\",\"dataType\":\"currency\",\"summaryType\":\"sum\",\"_id\":23,\"_pid\":-1,\"_level\":0,\"visible\":true,\"allowResize\":true,\"allowMove\":true,\"allowSort\":false,\"allowDrag\":false,\"readOnly\":false,\"autoEscape\":false,\"enabled\":true,\"vtype\":\"\",\"inited\":true,\"_gridUID\":\"mini-19\",\"_rowIdField\":\"_id\",\"_index\":20,\"summaryValue\":0,\"rowspan\":2,\"colspan1\":1,\"colspan2\":1,\"colspan0\":1,\"viewIndex2\":true},{\"header\":\"保安费\",\"name\":\"protect_cost\",\"field\":\"protect_cost\",\"width\":\"60px\",\"headerAlign\":\"center\",\"align\":\"center\",\"dataType\":\"currency\",\"summaryType\":\"sum\",\"_id\":24,\"_pid\":-1,\"_level\":0,\"visible\":true,\"allowResize\":true,\"allowMove\":true,\"allowSort\":false,\"allowDrag\":false,\"readOnly\":false,\"autoEscape\":false,\"enabled\":true,\"vtype\":\"\",\"inited\":true,\"_gridUID\":\"mini-19\",\"_rowIdField\":\"_id\",\"_index\":21,\"summaryValue\":0,\"rowspan\":2,\"colspan1\":1,\"colspan2\":1,\"colspan0\":1,\"viewIndex2\":true},{\"columns\":[{\"header\":\"报关费合\",\"field\":\"apply_cost\",\"width\":\"60px\",\"headerAlign\":\"center\",\"align\":\"center\",\"dataType\":\"currency\",\"summaryType\":\"sum\",\"_id\":26,\"_pid\":25,\"_level\":1,\"visible\":true,\"allowResize\":true,\"allowMove\":true,\"allowSort\":false,\"allowDrag\":false,\"readOnly\":false,\"autoEscape\":false,\"enabled\":true,\"vtype\":\"\",\"inited\":true,\"_gridUID\":\"mini-19\",\"_rowIdField\":\"_id\",\"_index\":22,\"summaryValue\":118,\"colspan1\":1,\"colspan2\":1,\"colspan0\":1,\"viewIndex2\":true},{\"header\":\"港建费合\",\"field\":\"wharf_cost1\",\"width\":\"60px\",\"headerAlign\":\"center\",\"align\":\"center\",\"dataType\":\"currency\",\"summaryType\":\"sum\",\"_id\":27,\"_pid\":25,\"_level\":1,\"visible\":true,\"allowResize\":true,\"allowMove\":true,\"allowSort\":false,\"allowDrag\":false,\"readOnly\":false,\"autoEscape\":false,\"enabled\":true,\"vtype\":\"\",\"inited\":true,\"_gridUID\":\"mini-19\",\"_rowIdField\":\"_id\",\"_index\":23,\"summaryValue\":0,\"colspan1\":1,\"colspan2\":1,\"colspan0\":1,\"viewIndex2\":true},{\"header\":\"总计\",\"field\":\"total\",\"width\":\"60px\",\"headerAlign\":\"center\",\"align\":\"center\",\"dataType\":\"currency\",\"summaryType\":\"sum\",\"_id\":28,\"_pid\":25,\"_level\":1,\"visible\":true,\"allowResize\":true,\"allowMove\":true,\"allowSort\":false,\"allowDrag\":false,\"readOnly\":false,\"autoEscape\":false,\"enabled\":true,\"vtype\":\"\",\"inited\":true,\"_gridUID\":\"mini-19\",\"_rowIdField\":\"_id\",\"_index\":24,\"summaryValue\":118,\"colspan1\":1,\"colspan2\":1,\"colspan0\":1,\"viewIndex2\":true}],\"header\":\"汇总\",\"headerAlign\":\"center\",\"_id\":25,\"_pid\":-1,\"_level\":0,\"width\":\"100px\",\"visible\":true,\"allowResize\":true,\"allowMove\":true,\"allowSort\":false,\"allowDrag\":false,\"readOnly\":false,\"autoEscape\":false,\"enabled\":true,\"vtype\":\"\",\"inited\":true,\"_gridUID\":\"mini-19\",\"_rowIdField\":\"_id\",\"colspan\":3,\"colspan1\":0,\"colspan2\":3,\"colspan0\":0,\"viewIndex2\":true},{\"header\":\"报关员\",\"field\":\"bg_name\",\"width\":\"60px\",\"headerAlign\":\"center\",\"align\":\"center\",\"_id\":29,\"_pid\":-1,\"_level\":0,\"visible\":true,\"allowResize\":true,\"allowMove\":true,\"allowSort\":false,\"allowDrag\":false,\"readOnly\":false,\"autoEscape\":false,\"enabled\":true,\"vtype\":\"\",\"inited\":true,\"_gridUID\":\"mini-19\",\"_rowIdField\":\"_id\",\"_index\":25,\"summaryValue\":\"\",\"rowspan\":2,\"colspan1\":1,\"colspan2\":1,\"colspan0\":1,\"viewIndex2\":true}]";
            var htmstr = share.GetComplexExcelHeardStr(data, str);
        }
    }
}
